Here's my attempt at a lay explanation for what's going on...

The reason that depth of field exists, i.e. the reason every photo doesn't have every single object tack sharp is that light coming in at different angles will be bent different amounts by the lens and come into focus at different distances behind the lens.

There are many factors which influence depth of field, but ultimately it's about the relative angles of incoming light. You can determine where the light comes into focus for a particular angle by focussing the lens, but you can't do anything about the difference in angle between the front and back of an object.

Imagine a triangle a 100 meters long and a few meters high. The slope will be very shallow. Now imagine another triangle 101 meters long and the same height. The slope will be almost identical. The slope represents the angle of incoming light from an object about 1 meter long at a distance of 100 meters.

Now imagine a triangle 2 meters long and 1 meter high, and a triangle 1 meter long and 1 meter high. The slopes are now very different, being 45 degrees and 27 degrees. Because the two angles of incidence in the first case were almost the same, with a 1 meter long object at 100 meters you can set the focus on the lens to get the front and back in focus.

But when the same object is much closer to the camera, you can either have the front or the back in focus, but not both, because the angles are different.

The aperture controls how much blur exists for light at different angles, but no aperture setting can eliminate blur if the angles are very different. Thus focus distance is the most important property when determining depth of field, so with close up (macro) photography you can get shallow depth of field with almost any aperture because of how quickly the incoming light angles change.