A piano is particularly easy to tell if it is out of tune because most notes have multiple strings. They beat against each other horrendously when one of the strings is at a different frequency than the other. If all the strings for a single note were out of tune by the same amount most people would think the piano was fine.
Can most people tell when a guitar is out of tune? A guitar so badly out of tune it plays different notes is recognizable by almost everyone, but a guitar only a little out of tune would not be noticed by most, IME.
At the same time: even if people can't tune a piano they can usually fairly reliably tell when one is out of tune.
But more impressive than absolute pitch to me is to be able to identify a four-note chord at once.